########################################################################
#  3account - personal finance data-base
#  Copyright (C) 2007,8,9 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301,USA.
########################################################################
"""Convert Direct-Net checking transactions CSV file to flat-OFX-RDF and
"""
from csv2flt import *
import re
from xls2csv2 import xls2csv2
from n3tagvalue import *
import logging

def acctidre(name):
    return '(?P<%s>[\d\-]+)'%name

htf_list=[
# version 0
([
    ['Exported from Direct Net on ',eurdtzre('DTASOF'),'','','',''],
    ['Booking entries','','','','',''],
    ['Account number',acctidre('ACCTID'),'','','',''],
    ['Balance',currencyre('CURRENCY')+' '+commanumre('UNITS'),'','','',''],
    ['Holder',strre('OWNER'),'','','',''],
    ['Bank',strre('ORG'),'','','',''],
    ['Description',strre('ACCTMEMO'),'','','',''],
    ['Current date',eurdtzre('DTASOF'),'','','',''],
    ['Booking entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+
     ' \(including provisional bookings\)','','','','',''],
    ['Booking date','Text','Debit','Credit','Value date','Balance'],
],
[eurdre('DTSETTLE'),
 strre('MEMO'),
 pvaluere('UNITS_debit')+'|', # The | is to allow also blank cells
 pvaluere('UNITS_credit')+'|',
 eurdre('DTAVAIL'),
 valuere('TOTAL')+'|'],
[['Total of column','',valuere('UNITS_total_debit'),
  valuere('UNITS_total_credit'),'','']]
),
# version 1 introduced somewhere 070501 and 070626
([
    ['Exported from Direct Net on',eurdtzre('DTASOF'),'','','',''],
    ['Booking Entries','','','','',''],
    ['Account Number',acctidre('ACCTID'),'','','',''],
    ['Balance',currencyre('CURRENCY')+' '+commanumre('UNITS'),'','','',''],
    ['Holder',strre('OWNER'),'','','',''],
    ['Bank',strre('ORG'),'','','',''],
    ['Description',strre('ACCTMEMO'),'','','',''],
    ['Current Date ',eurdtzre('DTASOF'),'','','',''],
    ['Booking Entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+
     ' \(Including Provisional Bookings\)','','','','',''],
    ['Booking Date','Text','Debit','Credit','Value Date','Balance'],
],
[eurdre('DTSETTLE'),
 strre('MEMO'),
 pvaluere('UNITS_debit')+'|', # The | is to allow also blank cells
 pvaluere('UNITS_credit')+'|',
 eurdre('DTAVAIL'),
 valuere('TOTAL')+'|'],
[['Total of Column','',valuere('UNITS_total_debit'),
  valuere('UNITS_total_credit'),'','']]
),
# version 2 introduced before 070910
([
    ['Exported from Direct Net on',eurdtzre('DTASOF'),'','','',''],
    ['Booking Entries','','','','',''],
    ['Account Number',acctidre('ACCTID'),'','','',''],
    ['Balance',currencyre('CURRENCY')+' '+commanumreint('UNITS'),'','','',''],
    ['IBAN',strre('ACCTID'),'','','',''],
    ['Bank',strre('ORG'),'','','',''],
    ['Holder',strre('OWNER'),'','','',''],
    ['Description',strre('ACCTMEMO'),'','','',''],
    ['Booking Entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+
     ' \(Including Provisional Bookings\)','','','','',''],
    ['Booking Date','Text','Debit','Credit','Value Date','Balance'],
],
[eurdre('DTSETTLE'),
 strre('MEMO'),
 pvaluereint('UNITS_debit')+'|', # The | is to allow also blank cells
 pvaluereint('UNITS_credit')+'|',
 eurdre('DTAVAIL'),
 valuereint('TOTAL')+'|'],
[['Total of Column','',valuereint('UNITS_total_debit'),
  valuereint('UNITS_total_credit'),'','']]
),
# version 3
([
    ['Exported from Direct Net on '+eurdtzre('DTASOF'),'','','','','',''],
    ['Bookings','','','','','',''],
    
    ['Account Details ','','','','','',''],
    ['Account',strre('ACCTID')+' Current account, '+strre('OWNER'),'','Balance',
     currencyre('CURRENCY')+' '+valuere('UNITS')],
    ['IBAN',strre('ACCTID'),'',strre('ACCTMEMO'),strre('ACCTMEMO1')],
    
    ['Booking Entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+
     ' \(Including Provisional Bookings\)','','',''],
    ['Booking Date','Text','Debit','Credit','Value Date','Balance'],
],
[eurdre('DTSETTLE'),
 strre('MEMO'),
 pvaluereint('UNITS_debit')+'|', # The | is to allow also blank cells
 pvaluereint('UNITS_credit')+'|',
 eurdre('DTAVAIL'),
 valuereint('TOTAL')+'|'],
[['Total of Column','',valuereint('UNITS_total_debit'),
  valuereint('UNITS_total_credit'),'','']]
),
]

def flat(csvfile,fout,context=None,hashing=False):
    n3header(fout,"$Id$"[1:-1],context)

    h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)
    line_num=len(htf_list[version][0])
    hl=0
    if version<3:
        l=h[hl]
        hl+=1
        dtasof = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']+\
               l['DTASOF_hours']+l['DTASOF_min']+l['DTASOF_TZ']
        hl+=1
    
        acctid=h[hl]['ACCTID']
        hl+=1
    
        currency=h[hl]['CURRENCY']
        units=h[hl]['UNITS'].replace(',','')
        hl+=1
    
        if version>=2:
            acctid1='IBEN='+h[hl]['ACCTID']
            hl+=1
    
        if version<2:
            owner=h[hl]['OWNER']
            hl+=1
            org=h[hl]['ORG']
            hl+=1
        else:
            org=h[hl]['ORG']
            hl+=1
            owner=h[hl]['OWNER']
            hl+=1
    
        acctmemo=h[hl]['ACCTMEMO']
        hl+=1
    
        if version<2:
            l=h[hl]
            hl+=1
            dtasof1 = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']+\
                    l['DTASOF_hours']+l['DTASOF_min']+l['DTASOF_TZ']
            if dtasof != dtasof1:
                print 'Warning Export date and Current date dont match'
    
        l=h[hl]
        hl+=1
        dtstart = l['DTSTART_year']+l['DTSTART_month']+l['DTSTART_day']
        dtend = l['DTEND_year']+l['DTEND_month']+l['DTEND_day']
    else:
        l=h[0]
        dtasof = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']+\
               l['DTASOF_hours']+l['DTASOF_min']+l['DTASOF_TZ']
        l=h[3]
        acctid=l['ACCTID']
        owner=l['OWNER']
        currency=l['CURRENCY']
        units=l['UNITS'].replace(',','')
        l=h[4]
        acctid1=l['ACCTID']
        acctmemo=l['ACCTMEMO']
        l=h[5]
        dtstart = l['DTSTART_year']+l['DTSTART_month']+l['DTSTART_day']
        dtend = l['DTEND_year']+l['DTEND_month']+l['DTEND_day']
    curdef=currency
    accttype='CHECKING'
    secid=currency

    print >>fout,"a3:flatLine [ ",
    print >>fout,stagvalue('ACCTID',acctid,hashing=hashing),
    print >>fout,stagvalue('CURDEF',curdef),
    print >>fout,stagvalue('ACCTTYPE',accttype),
    print >>fout,"];"


    print >>fout,"a3:flatLine [ ",
    print >>fout,stagvalue('ACCTID',acctid,hashing=hashing)
    print >>fout,stagvalue('DTASOF',dtasof),stagvalue('UNITS',units),
    print >>fout,"];"

    total_debit=0
    total_credit=0
    previouse_dtsettle=None
    for l in t:
        line_num+=1
        print >>fout,"a3:flatLine [ ",
        dtposted= l['DTSETTLE_year']+l['DTSETTLE_month']+l['DTSETTLE_day']
        assert len(dtposted)==8
        # There can be multiple transactions on the same day
        # (each will have a different NLINE so it will be taken to be a
        # different transaction) The newest appear first.
        assert previouse_dtsettle==None or previouse_dtsettle>=dtposted
        previouse_dtsettle=dtposted

        # TODO: what is dtvalue (it is not DTSETTLE or DTTRADE, I checkecked
        # information on individual transactions.)
        dtavail= l['DTAVAIL_year']+l['DTAVAIL_month']+l['DTAVAIL_day']
        assert len(dtavail)==8

        memo=l['MEMO']

        debit=l['UNITS_debit']
        credit=l['UNITS_credit']
        if debit and credit:
            logging.error("Debit and Credit in same line %d"%line_num)
            raise Exception()
        if debit:
            debit=float(debit)
            total_debit+=debit
            trnamt=-debit
            trntype='DEBIT'
        elif credit:
            credit=float(credit)
            total_credit+=credit
            trnamt=credit
            trntype='CREDIT'
        else:
            logging.error("no credit or debit %d"%line_num)
            raise Exception()

        strn=[]
        strn.append(stagvalue('ACCTID',acctid,hashing=hashing))
        strn.append(stagvalue('DTPOSTED',dtposted))
        strn.append(stagvalue('TRNTYPE',trntype))
        if units: strn.append(stagvalue('TRNAMT',trnamt))
        strn=" ".join(strn)
        print >>fout,stagvalue('FITID',hash(strn)),
        print >>fout,strn,
        
        print >>fout,stagvalue('DTASOF',dtasof),
        print >>fout,stagvalue('DTAVAIL',dtavail),
        memo1=memo[:45].strip()
        memo2=memo[45:].strip()
        print >>fout,stagvalue('mEMO',memo1,hashing=hashing),
        if memo2:
            print >>fout,stagvalue('mEMO',memo2,hashing=hashing),
        # using match force the number to appear at the start
        t_units=re.match( ',?(?P<U>\d{1,3}(,\d{3})*) [A-Z0-9]',
                          memo2)
        if t_units:
            t_units=t_units.group('U')
            secname=memo2[len(t_units)+1:].strip()
            t_units=float(t_units.replace(',',''))
            if not secname:
                logging.error('missing SECNAME %d'%line_num)
                raise Exception()
        else:
            secname=None
        if memo1 in ['Securities purchase', 'Deposit', 'Reversal Redemption']:
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','BUY'),
            if t_units:
                print >>fout,stagvalue('units',t_units),
            else:
                logging.warning('%s','Units missing')
        elif memo1 in ['Reverse split', 'split','Stock dividend/spin-off']:
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','SPLIT'),
            if t_units:
                print >>fout,stagvalue('units',t_units),
            else:
                logging.warning('%s','Units missing')
        elif memo1 in ['Securities sale',
                       'Redemption','Redemption of fund units',
                       'Capital repayment','Custody account withdrawal']:
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','SELL'),
            if t_units:
                print >>fout,stagvalue('units',-t_units),
            else:
                logging.info('Missing units')                
        elif memo1=='Cash dividend':
            if not t_units:
                logging.error('Missing units %s %d'%(memo1,line_num))
                raise Exception()
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','DIV'),
            print >>fout,stagvalue('units',t_units),
        elif memo1=='Capital gain':
            if not t_units:
                logging.error('Missing units %s %d'%(memo1,line_num))
                raise Exception()
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','CGAIN'),
            print >>fout,stagvalue('units',t_units),
        elif memo1=='Interest payment' or memo1=='Equalisation payment':
            if not t_units:
                logging.error('Missing units %s %d'%(memo1,line_num))
                raise Exception()
            print >>fout,stagvalue('SECNAME',secname),
            print >>fout,stagvalue('NAME',secname,hashing=hashing),
            print >>fout,stagvalue('tran','INT'),
            print >>fout,stagvalue('units',t_units),
        elif memo1=='Fid. call deposit int. settlemt.':
            print >>fout,stagvalue('tran','INT'),
            print >>fout,stagvalue('NAME',memo2,hashing=hashing), # XACCTID
        elif memo1 in ['Fiduciary call deposit', 
                       'Fiduciary call deposit -  increase']:
            print >>fout,stagvalue('tran','CREDIT'),
            print >>fout,stagvalue('NAME',memo2,hashing=hashing), #XACCTID
        elif memo1 in ['Fiduciary call dep. -  liquidation']:
            print >>fout,stagvalue('tran','DEBIT'),
            print >>fout,stagvalue('NAME',memo2,hashing=hashing), #XACCTID
        elif memo1=='Payment order' and memo2.startswith('MANAGEMENT FEE'):
            pass
        elif memo1 in ['Portfolio management fee',
                       'Balance settlement of expenses',
                       'Reversal Portfolio management fee',
                       'Administration fees','Reversal Administration fees',
                       'Charges for tax documentation' ,
                       'Balance of closing entries','Lending commission',
                       'Reversal Charges for tax documentati']:
            pass
        elif memo1 in ['Transfer', 'Payment order','Payment']:
            o=re.match(',?(?P<DIR>IN|EX) '+
                       acctidre('ACCTID')+
                       ' AT (?P<XRATE>\d+\.\d+)',memo2)
            if o:
                if o.group('DIR')=='IN' and units>0:
                    logging.info('Wrong direction %d'%line_num)
                print >>fout,stagvalue('NAME',o.group('ACCTID'), #XACCTID
                                       hashing=hashing),
                print >>fout,stagvalue('xrate',o.group('XRATE')),
            else:
                logging.warning('%s','missing direction and account')
        else:
            print 'Unknown transaction',memo1
        print >>fout,"];"
    print >>fout,"."

    if (abs(float(f[0]['UNITS_total_credit'])-total_credit)>1 or
        abs(float(f[0]['UNITS_total_debit'])-total_debit)>1):
        print 'Warning total dont match',f[0]['UNITS_total_credit'],
        print total_credit,f[0]['UNITS_total_debit'],total_debit
